package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class WebsiteReportOrdersDAO implements java.io.Serializable{

	Connection con;

	public WebsiteReportOrdersDAO(Connection connection){
		con = connection;
	}

	public int[] addWebsiteReportOrders(int websiteOrderId, int arrReportTitleIds[])throws SQLException{

		PreparedStatement pstmt = con.prepareStatement("INSERT INTO WEBSITE_REPORT_ORDERS(WEBSITE_ORDERS_ID,REPORT_TITLES_ID,REQUEST_DATE)VALUES(?,?,CURRENT_DATE)");

		for(int i=0;i<arrReportTitleIds.length;i++){
			//System.out.println(websiteOrderId + " , " + arrReportTitleIds[i]);
			pstmt.setInt(1,websiteOrderId);
			pstmt.setInt(2,arrReportTitleIds[i]);
			pstmt.addBatch();
		}
		int[] updateCols = pstmt.executeBatch();
		pstmt.close();
		return updateCols;
	}

	public List getReportTitlesByOrderId(int orderId)throws SQLException{
		List lstReports = new ArrayList();

		PreparedStatement pstmt = con.prepareStatement("SELECT RT.REPORT_TITLES_ID AS REPORT_TITLES_ID,RT.REPORT_TITLE AS REPORT_TITLE FROM REPORT_TITLES RT, WEBSITE_REPORT_ORDERS WRO WHERE RT.REPORT_TITLES_ID = WRO.REPORT_TITLES_ID AND WRO.WEBSITE_ORDERS_ID = ?");
		pstmt.setInt(1,orderId);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			ReportTitlesBean rtbean = new ReportTitlesBean();
			rtbean.setReportTitlesId(rs.getInt(1));
			rtbean.setReportTitle(rs.getString(2));
			lstReports.add(rtbean);
		}
		rs.close();
		pstmt.close();

		return lstReports;
	}

	public int deleteWebsiteReportOrdersByOrderId(int orderId)throws SQLException{
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_REPORT_ORDERS WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setInt(1,orderId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}
	/*
	public WebsiteReportOrdersDAO(){
	}

	Connection getConnection()throws SQLException{
		//Put your implementation-specific connection code here !
		return null;
	}

	void returnConnection(Connection con)throws SQLException{
		//Put your implementation-specific connection code here !
	}


	public WebsiteReportOrdersBean selectWebsiteReportOrdersByPK(WebsiteReportOrdersBean bean)throws SQLException{

		Connection con = getConnection();
		PreparedStatement pstmt = con.prepareStatement("SELECT WEBSITE_REPORT_ORDERS_ID,WEBSITE_ORDERS_ID,REPORT_TITLES_ID,REQUEST_DATE FROM WEBSITE_REPORT_ORDERS WHERE WEBSITE_REPORT_ORDERS_ID = ? ");
		pstmt.setInt(1,bean.getWebsiteReportOrdersId());
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			WebsiteReportOrdersBean bn = new WebsiteReportOrdersBean();
			bn.setWebsiteReportOrdersId(rs.getInt("WEBSITE_REPORT_ORDERS_ID"));
			bn.setWebsiteOrdersId(rs.getInt("WEBSITE_ORDERS_ID"));
			bn.setReportTitlesId(rs.getInt("REPORT_TITLES_ID"));
			bn.setRequestDate(rs.getDate("REQUEST_DATE"));
			returnConnection(con);
			return bn;
		}else{
			returnConnection(con);
			return null;
		}
	}

	public DbResultSet selectWebsiteReportOrdersRecords(String whereClause,String orderByClause)throws SQLException{

		ArrayList records = new ArrayList();
		Connection con = getConnection();
		String sql ="SELECT WEBSITE_REPORT_ORDERS_ID,WEBSITE_ORDERS_ID,REPORT_TITLES_ID,REQUEST_DATE FROM WEBSITE_REPORT_ORDERS";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			WebsiteReportOrdersBean bn = new WebsiteReportOrdersBean(rs.getInt("WEBSITE_REPORT_ORDERS_ID"),rs.getInt("WEBSITE_ORDERS_ID"),rs.getInt("REPORT_TITLES_ID"),rs.getDate("REQUEST_DATE"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		returnConnection(con);
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public int addWebsiteReportOrders(WebsiteReportOrdersBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("INSERT INTO WEBSITE_REPORT_ORDERS(WEBSITE_REPORT_ORDERS_ID,WEBSITE_ORDERS_ID,REPORT_TITLES_ID,REQUEST_DATE)VALUES(?,?,?,?)");
		pstmt.setInt(1,bean.getWebsiteReportOrdersId());
		pstmt.setInt(2,bean.getWebsiteOrdersId());
		pstmt.setInt(3,bean.getReportTitlesId());
		pstmt.setDate(4,bean.getRequestDate());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}

	public int updateWebsiteReportOrders(WebsiteReportOrdersBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_REPORT_ORDERS SET WEBSITE_ORDERS_ID = ? ,REPORT_TITLES_ID = ? ,REQUEST_DATE = ?  WHERE WEBSITE_REPORT_ORDERS_ID = ?");
		pstmt.setInt(1,bean.getWebsiteOrdersId());
		pstmt.setInt(2,bean.getReportTitlesId());
		pstmt.setDate(3,bean.getRequestDate());
		pstmt.setInt(4,bean.getWebsiteReportOrdersId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}

	public int deleteWebsiteReportOrders(WebsiteReportOrdersBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_REPORT_ORDERS WHERE WEBSITE_REPORT_ORDERS_ID = ?");
		pstmt.setInt(1,bean.getWebsiteReportOrdersId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}
	*/
}